package com.mobile.learning.lib;

import java.util.ArrayList;
import java.util.List;

import android.R.integer;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DBClass extends SQLiteOpenHelper {
	private static final String DATABASE_NAME = "db_mobilelearning";

	public DBClass(Context context) {
		super(context, DATABASE_NAME, null, 1);
		// TODO Auto-generated constructor stub
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub

		if (!db.isReadOnly()) {
			// Enable foreign key constraints
			db.execSQL("PRAGMA foreign_keys=ON;");
		}

		db.execSQL("CREATE TABLE if not exists tb_jenis "
				+ "(id_jenis INTEGER PRIMARY KEY, " + "jenis_soal TEXT, "
				+ "kode_jenis TEXT, " + "nama_jenis TEXT, "
				+ "versi_soal TEXT);");

		db.execSQL("CREATE TABLE if not exists tb_soal "
				+ "(id_soal INTEGER PRIMARY KEY, " + "text_soal TEXT, "
				+ "id_jenis INTEGER, " + "filegambar TEXT, " + "FOREIGN KEY("
				+ "id_jenis" + ") REFERENCES " + "tb_jenis(" + "id_jenis"
				+ "));");

		db.execSQL("CREATE TABLE if not exists tb_jawaban "
				+ "(id_jawaban INTEGER PRIMARY KEY, " + "text_jawaban TEXT, "
				+ "val TEXT, " + "id_soal INTEGER, " + "filegambar TEXT, "
				+ "FOREIGN KEY(" + "id_soal" + ") REFERENCES " + "tb_soal("
				+ "id_soal" + "));");

		db.execSQL("CREATE TABLE if not exists tb_siswa "
				+ "(id_siswa INTEGER PRIMARY KEY AUTOINCREMENT, "
				+ "nomor_induk TEXT, " + "nama TEXT, " + "password TEXT, "
				+ "logdate DATE);");

		db.execSQL("CREATE TABLE if not exists tb_nilai "
				+ "(id_nilai INTEGER PRIMARY KEY AUTOINCREMENT, "
				+ "tgl_pengerjaan DATE, " + "nilai INTEGER, "
				+ "id_jenis INTEGER);");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub

		db.execSQL("DROP TABLE IF EXISTS tb_siswa");
		db.execSQL("DROP TABLE IF EXISTS tb_jenis");
		db.execSQL("DROP TABLE IF EXISTS tb_soal");
		db.execSQL("DROP TABLE IF EXISTS tb_jawaban");
		db.execSQL("DROP TABLE IF EXISTS tb_nilai");
		onCreate(db);

	}

	public void InsertSiswa(Login login) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();

		values.put("id_siswa", login.getId_siswa());
		values.put("nomor_induk", login.getNomor_induk());
		values.put("nama", login.getNama());
		values.put("password", login.getPassword());
		values.put("logdate", login.getLogdate().toString());
		db.insert("tb_siswa", null, values);
		db.close();

	}

	public void InsertJenis(Jenis jenis) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put("id_jenis", jenis.getId_jenis());
		values.put("jenis_soal", jenis.getJenis_soal());
		values.put("kode_jenis", jenis.getKode_jenis());
		values.put("nama_jenis", jenis.getNama_jenis());
		values.put("versi_soal", jenis.getVersi_soal());
		db.insert("tb_jenis", null, values);
		db.close();
	}

	public void InsertSoal(Soal soal) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put("id_soal", soal.getId_soal());
		values.put("text_soal", soal.getText_soal());
		values.put("id_jenis", soal.getId_jenis());
		values.put("filegambar", soal.getFilegambar());
		db.insert("tb_soal", null, values);
		db.close();
	}

	public void InsertJawaban(Jawaban jawaban) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put("id_jawaban", jawaban.getId_jawaban());
		values.put("text_jawaban", jawaban.getText_jawaban());
		values.put("val", jawaban.getVal());
		values.put("id_soal", jawaban.getId_soal());
		values.put("filegambar", jawaban.getFilegambar());
		db.insert("tb_jawaban", null, values);
		db.close();
	}

	public void insertNilai(Nilai nilai) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put("tgl_pengerjaan", nilai.getTgl_pengerjaan());
		values.put("nilai", nilai.getNilai());
		values.put("id_jenis", nilai.getId_jenis());
		db.insert("tb_nilai", null, values);
		db.close();
	}

	public void deleteSiswa() {
		SQLiteDatabase db = this.getWritableDatabase();
		String str = "DELETE from tb_siswa";
		db.execSQL(str);
		db.close();

	}

	public void deleteJenis() {
		SQLiteDatabase db = this.getWritableDatabase();
		String str = "DELETE from tb_jenis";
		db.execSQL(str);
		db.close();
	}

	public void deleteSoal() {
		SQLiteDatabase db = this.getWritableDatabase();
		String str = "DELETE from tb_soal";
		db.execSQL(str);
		db.close();
	}

	public void deleteJawaban() {
		SQLiteDatabase db = this.getWritableDatabase();
		String str = "DELETE from tb_jawaban";
		db.execSQL(str);
		db.close();
	}

	public boolean isLogin() {
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor c = db.rawQuery(
				"SELECT id_siswa, nomor_induk, nama, password, logdate "
						+ "FROM tb_siswa LIMIT 1", new String[] {});

		if (c != null)
			c.moveToFirst();
		int count = c.getCount();
		c.close();
		db.close();
		if (count > 0) {
			return true;
		} else {
			return false;
		}

	}

	public Login getLogin() {
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor c = db.rawQuery(
				"SELECT id_siswa, nomor_induk, nama, password, logdate "
						+ "FROM tb_siswa LIMIT 1", new String[] {});

		if (c != null)
			c.moveToFirst();
		Login login = new Login(c.getInt(0), c.getString(1), c.getString(2),
				c.getString(3), c.getString(4));

		c.close();
		db.close();

		return login;

	}



	public List<Jenis> getAllJenis(String jenis) {
		List<Jenis> dataList = new ArrayList<Jenis>();
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cur = db.rawQuery(
				"Select id_jenis, jenis_soal, kode_jenis, nama_jenis, versi_soal "
						+ "from tb_jenis Where jenis_soal='" + jenis
						+ "' Order by id_jenis Desc", null);
		if (cur.moveToFirst()) {
			do {
				Jenis c = new Jenis();
				c.setId_jenis(Integer.parseInt(cur.getString(cur
						.getColumnIndex("id_jenis"))));
				c.setJenis_soal(cur.getString(cur.getColumnIndex("jenis_soal")));
				c.setKode_jenis(cur.getString(cur.getColumnIndex("kode_jenis")));
				c.setNama_jenis(cur.getString(cur.getColumnIndex("nama_jenis")));
				c.setVersi_soal(cur.getString(cur.getColumnIndex("versi_soal")));

				dataList.add(c);
			} while (cur.moveToNext());

		}
		cur.close();
		db.close();
		return dataList;
	}
	
	public List<Nilai> getAllNilai(){
		List<Nilai> dataList = new ArrayList<Nilai>();
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cur = db.rawQuery("SELECT a.id_nilai, a.tgl_pengerjaan, b.nama_jenis, b.jenis_soal, a.nilai FROM tb_nilai a INNER JOIN tb_jenis b ON a.id_jenis = b.id_jenis", null);
		if(cur.moveToFirst()){
			do{
				Nilai n = new Nilai();
				n.setId_nilai(Integer.parseInt(cur.getString(cur.getColumnIndex("id_nilai"))));
				n.setTgl_pengerjaan(cur.getString(cur.getColumnIndex("tgl_pengerjaan")));
				n.setSoal(cur.getString(cur.getColumnIndex("nama_jenis")));
				n.setJenis(cur.getString(cur.getColumnIndex("jenis_soal")));
				n.setNilai(Integer.parseInt(cur.getString(cur.getColumnIndex("nilai"))));
			
				dataList.add(n);
			}while (cur.moveToNext());
		} 
		cur.close();
		db.close();
		return dataList;
	}

	public Soal getJumlahSoal(int idjenis) {
		Soal s = new Soal();
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cur = db.rawQuery(
				"SELECT COUNT(*) as jml FROM tb_soal WHERE id_jenis='"
						+ idjenis + "' LIMIT 1", null);
		if (cur != null)
			cur.moveToFirst();
		s.setJml_soal(Integer.parseInt(cur.getString(cur.getColumnIndex("jml"))));
		cur.close();
		db.close();
		return s;
	}

	public DataSoal getSoal(int idjenis, int soal_ke) {
		DataSoal ds = new DataSoal();
		int no = 0;
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cur = db
				.rawQuery(
						"SELECT a.id_soal, a.text_soal, a.id_jenis, a.filegambar AS gambarsoal, b.id_jawaban, b.text_jawaban, b.val, b.filegambar AS gambarjawaban  FROM tb_soal a INNER JOIN tb_jawaban b ON a.id_soal=b.id_soal WHERE a.id_jenis='"
								+ String.valueOf(idjenis)
								+ "' LIMIT "
								+ String.valueOf(soal_ke) + ",4", null);

		if (cur.moveToFirst()) {
			ds.setId_soal(Integer.parseInt(cur.getString(cur
					.getColumnIndex("id_soal"))));
			ds.setText_soal(cur.getString(cur.getColumnIndex("text_soal")));
			ds.setId_jenis(Integer.parseInt(cur.getString(cur
					.getColumnIndex("id_jenis"))));
			ds.setGambarsoal(cur.getString(cur.getColumnIndex("gambarsoal")));
			ds.setText_jawaban_a(cur.getString(cur
					.getColumnIndex("text_jawaban")));
			ds.setText_jawaban_b(cur.getString(cur
					.getColumnIndex("text_jawaban")));
			ds.setText_jawaban_c(cur.getString(cur
					.getColumnIndex("text_jawaban")));
			ds.setText_jawaban_d(cur.getString(cur
					.getColumnIndex("text_jawaban")));
			ds.setGambarjawaban_a(cur.getString(cur
					.getColumnIndex("gambarjawaban")));
			ds.setGambarjawaban_b(cur.getString(cur
					.getColumnIndex("gambarjawaban")));
			ds.setGambarjawaban_c(cur.getString(cur
					.getColumnIndex("gambarjawaban")));
			ds.setGambarjawaban_d(cur.getString(cur
					.getColumnIndex("gambarjawaban")));
			ds.setVal_a(cur.getString(cur.getColumnIndex("val")));
			ds.setVal_b(cur.getString(cur.getColumnIndex("val")));
			ds.setVal_c(cur.getString(cur.getColumnIndex("val")));
			ds.setVal_d(cur.getString(cur.getColumnIndex("val")));
			for (cur.moveToFirst(); !cur.isAfterLast(); cur.moveToNext()) {
				ds.setId_soal(Integer.parseInt(cur.getString(cur
						.getColumnIndex("id_soal"))));
				no++;
				ds.setText_soal(cur.getString(cur.getColumnIndex("text_soal")));
				ds.setId_jenis(Integer.parseInt(cur.getString(cur
						.getColumnIndex("id_jenis"))));
				ds.setGambarsoal(cur.getString(cur.getColumnIndex("gambarsoal")));
				if (no == 1)
					ds.setText_jawaban_a(cur.getString(cur
							.getColumnIndex("text_jawaban")));
				if (no == 2)
					ds.setText_jawaban_b(cur.getString(cur
							.getColumnIndex("text_jawaban")));
				if (no == 3)
					ds.setText_jawaban_c(cur.getString(cur
							.getColumnIndex("text_jawaban")));
				if (no == 4)
					ds.setText_jawaban_d(cur.getString(cur
							.getColumnIndex("text_jawaban")));
				if (no == 1)
					ds.setGambarjawaban_a(cur.getString(cur
							.getColumnIndex("gambarjawaban")));
				if (no == 2)
					ds.setGambarjawaban_b(cur.getString(cur
							.getColumnIndex("gambarjawaban")));
				if (no == 3)
					ds.setGambarjawaban_c(cur.getString(cur
							.getColumnIndex("gambarjawaban")));
				if (no == 4)
					ds.setGambarjawaban_d(cur.getString(cur
							.getColumnIndex("gambarjawaban")));
				if (no == 1)
					ds.setVal_a(cur.getString(cur.getColumnIndex("val")));
				if (no == 2)
					ds.setVal_b(cur.getString(cur.getColumnIndex("val")));
				if (no == 3)
					ds.setVal_c(cur.getString(cur.getColumnIndex("val")));
				if (no == 4)
					ds.setVal_d(cur.getString(cur.getColumnIndex("val")));
			}
		}
		cur.close();
		db.close();
		return ds;

	}

	public Jenis getJenis(int idjenis) {
		Jenis j = new Jenis();
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cur = db.rawQuery(
				"SELECT id_jenis, jenis_soal, kode_jenis, nama_jenis, versi_soal "
						+ "FROM tb_jenis WHERE id_jenis='" + idjenis
						+ "' LIMIT 1", null);
		if (cur != null)
			cur.moveToFirst();
		j.setId_jenis(Integer.parseInt(cur.getString(cur
				.getColumnIndex("id_jenis"))));
		j.setJenis_soal(cur.getString(cur.getColumnIndex("jenis_soal")));
		j.setKode_jenis(cur.getString(cur.getColumnIndex("kode_jenis")));
		j.setNama_jenis(cur.getString(cur.getColumnIndex("nama_jenis")));
		j.setVersi_soal(cur.getString(cur.getColumnIndex("versi_soal")));
		cur.close();
		db.close();
		return j;

	}

}
